# coding=utf-8
import pymysql.cursors
import os,sys
sys.path.append('..\\')
import configparser as cparser
import pymysql
sys.path.append('..\\..\\')
from common.choose_env import chooseEnv
import json

# ======== MySql base operating ===================
class DB:
    #选择数据库
    chenv = chooseEnv()
    #获得指定数据的db信息
    dbinfo = chenv.choose_db()
    # print(dbinfo)
    #数据库初始化连接
    def __init__(self):
        try:
            # Connect to the database
            self.connection = pymysql.connect(host=self.dbinfo.get('host'),
                                              user=self.dbinfo.get('user'),
                                              port=int(self.dbinfo.get('port')),
                                              password=self.dbinfo.get('password'),
                                              db=self.dbinfo.get('db_name'),
                                              charset='utf8mb4',
                                              cursorclass=pymysql.cursors.DictCursor)
          
        except pymysql.err.OperationalError as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))



    def insert(self, table_name, table_data):
        for key in table_data:
            table_data[key] = "'"+str(table_data[key])+"'"
        key   = ','.join(table_data.keys())
        value = ','.join(table_data.values())
        real_sql = "INSERT INTO " + table_name + " (" + key + ") VALUES (" + value + ")"
        with self.connection.cursor() as cursor:
            cursor.execute(real_sql)
        self.connection.commit()

    def find_sql(self,table_name,table_data,condition=''):
        real_sql = "select " + table_data  + " from " + table_name
        if bool(condition):
            real_sql+=" where " + condition
        with self.connection.cursor() as cursor:
            cursor.execute(real_sql)
            list1 = cursor.fetchall()
            # print (list1)
            return list1


    def find_sql2(self,table_name,table_data,table_code,table_info,table_desc,condition=''):
        real_sql = "select " + table_data + ','+ table_code + ',' + table_info + ',' + table_desc + " from " + table_name
        if bool(condition):
            real_sql+=" where " + condition
        with self.connection.cursor() as cursor:
            cursor.execute(real_sql)
            list1 = cursor.fetchall()
            # print (list1)
            return list1

    def close(self):
        self.connection.close()

if __name__ == '__main__':
    db = DB()
    # print (db.find_sql2('case_param','parameters','code','info','case_desc','case_id=1'))
    list = db.find_sql('case_param', 'parameters', 'case_id=6')
    print(list)
    # print(list[0])
    for cols in list:
        # print(cols)
        param = cols['parameters']
        # param = cols['user_id']
        # param = cols[0]
        print(param)
        # 把Json格式字符串解码转换成Python对象字典，因json字符串不能对内部内容操作
    paramDic = json.loads(param)
    print(paramDic)
    # print(paramDic['bid_money'])
        # paramDic = json.loads(param)
        # print(paramDic)
    param2 = paramDic['repay_id']
    print(param2)
    # paramDic = json.loads(param)
    # paramCode = cols['code']
    db.close()
